<?php 
/**
* @version		$Id: pgsql.adapter.php 1138 2012-05-18 17:13:22Z datahell $
* @package		Elxis
* @subpackage	Database
* @copyright	Copyright (c) 2006-2012 Elxis CMS (http://www.elxis.org). All rights reserved.
* @license		Elxis Public License ( http://www.elxis.org/elxis-public-license.html )
* @author		Elxis Team ( http://www.elxis.org )
* @description 	Elxis CMS is free software. Read the license for copyright notices and details
*/

defined('_ELXIS_') or die ('Direct access to this location is not allowed.');


class elxisPgsqlAdapter extends elxisDbAdapter {


	/*************************************/
	/* CALL THE PARENT CLASS CONSTRUCTOR */
	/*************************************/
	public function __construct($pdo=null) {
		parent::__construct($pdo);
	}


	/*************************************/
	/* ADD LIMIT/OFFSET TO SQL STATEMENT */
	/*************************************/
	public function addLimit($sql, $offset=-1, $limit=-1) {
		if ($limit <= 0) { return $sql; }
		$sql .= ' LIMIT '.$limit;
		if ($offset > 0) { $sql .= ' OFFSET '.$offset; }
		return $sql;
	}


	/*****************************************************************/
	/* GET THE LAST ID GENERATED BY AN IDENTITY/AUTOINCREMENT COLUMN */
	/*****************************************************************/
    public function lastInsertId($tableName = null, $primaryKey = null) {
        if ($tableName !== null) {
            $sequenceName = $tableName;
            if ($primaryKey) { $sequenceName .= '_'.$primaryKey; }
            $sequenceName .= '_seq';
			$sql = "SELECT CURRVAL(".$sequenceName.")";
			return (int)$this->pdo->query($sql)->fetchColumn();
        }
        return $this->pdo->lastInsertId($tableName);
    }


    /****************************/
	/* LIST ALL DATABASE TABLES */
	/****************************/
    public function listTables() {
    	$sql = "SELECT c.relname AS table_name FROM pg_class c, pg_user u"
		."\n WHERE c.relowner = u.usesysid AND c.relkind = 'r'"
		."\n AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) AND c.relname !~ '^(pg_|sql_)'"
		."\n UNION SELECT c.relname AS table_name FROM pg_class c"
		."\n WHERE c.relkind = 'r'"
		."\n AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname)"
		."\n AND NOT EXISTS (SELECT 1 FROM pg_user WHERE usesysid = c.relowner)"
		."\n AND c.relname !~ '^pg_'";
    	$stmt = $this->pdo->prepare($sql);
    	$stmt->execute();
    	return $stmt->fetchCol();
    }


	/************************************************/
	/* BACKUP DATABASE (EXPERIMENTAL - NOT TESTED!) */
	/************************************************/
	public function backup($params) {
		if (count($params) == 0) { return -2; }

		$elxis = eFactory::getElxis();
		$dsn = $elxis->getConfig('DB_DSN');
		if (trim($dsn) != '') {
			$dbname = $this->getFromDSN($dsn, 'dbname');
		} else {
			$dbname = $elxis->getConfig('DB_NAME');
		}
		if ($dbname == '') { return -2; }

		$com = '--';

		$out = $com."\n";
		$out .= $com.' PostgreSQL backup taken by Elxis CMS v'.$elxis->getVersion()."\n";
		$out .= $com.' Copyright (c) 2006-'.date('Y')." elxis.org. All rights reserved.\n";
		$out .= $com.' Database: '.$dbname."\n";
		$out .= $com.' URL: '.$elxis->getConfig('URL')."\n";
		$out .= $com.' Date (UTC): '.gmdate('Y-m-d H:i:s')."\n";
		$out .= $com."\n\n";

		if ($params['create_db'] == true) {
			$out .= 'CREATE DATABASE '.$dbname.';'."\n\n";
		}

		foreach ($params['tables'] as $table) {
			$sql = "SELECT attnum, attname, typname, atttypmod-4, attnotnull, atthasdef, adsrc AS def"
			."\n FROM pg_attribute, pg_class, pg_type, pg_attrdef WHERE pg_class.oid=attrelid AND pg_type.oid=atttypid"
			."\n AND attnum>0 AND pg_class.oid=adrelid AND adnum=attnum AND atthasdef='t' AND lower(relname)='".$table."'"
			."\n UNION SELECT attnum, attname, typname, atttypmod-4, attnotnull, atthasdef, '' AS def"
			."\n FROM pg_attribute, pg_class, pg_type WHERE pg_class.oid=attrelid AND pg_type.oid=atttypid"
			."\n AND attnum>0 AND atthasdef='f' AND lower(relname)='".$table."'";
			$query = $this->pdo->query($sql);
			if ($query === false) { continue; }

			$out .= $com."\n";
			$out .= $com.' Definition of table '.$table."\n";
			$out .= $com."\n\n";
 			if ($params['add_drop'] == true) {
				$out .= 'DROP TABLE '.$table.' CASCADE;'."\n";
			}

			$rows = $query->fetchAll(PDO::FETCH_NUM);
			if ($rows) {
				$n = count($rows);
				$k = 1;
				$out .= 'CREATE TABLE '.$table." (\n";
				foreach ($rows as $row) {
					$out .= $row[1].' '.$row[2];
					if ($row[2] == 'varchar') { $out .= '('.$row[3].')'; }
					if ($row[4] == 't') { $out .= ' NOT NULL'; }
					if ($row[5] == 't') { $out .= ' DEFAULT '.$row[6]; }
					if ($k < $n) { $out .= ','; }
					$out .= "\n";
					$k++;
				}
				$out .= ");\n\n";
			}

			$sql = "SELECT pg_index.indisprimary, pg_catalog.pg_get_indexdef(pg_index.indexrelid)"
			."\n FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index AS pg_index"
			."\n WHERE c.relname = '".$table."' AND c.oid = pg_index.indrelid"
			."\n AND pg_index.indexrelid = c2.oid AND pg_index.indisprimary";
			$query = $this->pdo->query($sql);
			if ($query !== false) {
				$rows = $query->fetchAll(PDO::FETCH_NUM);
				if ($rows) {
					$out .= $com."\n";
					$out .= $com.' Creating index for '.$table."\n";
					$out .= $com."\n\n";
					foreach ($rows as $row) {
						$t = str_replace("CREATE UNIQUE INDEX", "", $row[1]);
						$t = str_replace("USING btree", "|", $t);
						$t = str_replace("ON", "|", $t);
						$arr = explode("|", $t);
						$out .= "ALTER TABLE ONLY ".$arr[1]." ADD CONSTRAINT ".$arr[0]." PRIMARY KEY ".$arr[2].";\n";
					}
					$out .= "\n";
				}
			}

			if ($params['add_insert'] == false) { continue; }
			if (in_array($table, $params['no_insert_tables'])) { continue; }

 			$stmt = $this->pdo->prepare('SELECT * FROM '.$table);
			if (!$stmt->execute()) { $out .= "\n\n"; continue; }
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if (!$rows) { continue; }
			foreach ($rows as $row) {
				$i = 1;
				$n = count($row);
				$vstr = '';
				foreach($row as $k => $v) {
					if ($v === NULL) {
						$vstr .= 'NULL';
					} else if ($v == '') {
						$vstr .= 'NULL';
					} else if (is_int($v)) {
						$vstr .= $v;
					} else if (is_numeric($v) && (intval($v) == $v)) {
						$vstr .= $v;
					} else {
						$vstr .= '\''.addslashes($v).'\'';
					}
					if ($i < $n) { $vstr .= ', '; }
					$i++;
				}
				$out .= 'INSERT INTO '.$table.' VALUES ('.$vstr.');'."\n";
			}
			$out .= "\n";
		}

		$sql = "SELECT cl.relname AS tabela, ct.conname, pg_get_constraintdef(ct.oid) FROM pg_catalog.pg_attribute a"
		."\n JOIN pg_catalog.pg_class cl ON (a.attrelid = cl.oid AND cl.relkind = 'r')"
		."\n JOIN pg_catalog.pg_namespace n ON (n.oid = cl.relnamespace)"
		."\n JOIN pg_catalog.pg_constraint ct ON (a.attrelid = ct.conrelid AND ct.confrelid != 0 AND ct.conkey[1] = a.attnum)"
		."\n JOIN pg_catalog.pg_class clf ON (ct.confrelid = clf.oid AND clf.relkind = 'r')"
		."\n JOIN pg_catalog.pg_namespace nf ON (nf.oid = clf.relnamespace)"
		."\n JOIN pg_catalog.pg_attribute af ON (af.attrelid = ct.confrelid AND af.attnum = ct.confkey[1]) order by cl.relname";
		$query = $this->pdo->query($sql);
		if ($query !== false) {
			$rows = $query->fetchAll(PDO::FETCH_NUM);
			if ($rows) {
				foreach ($rows as $row) {
					$out .= $com."\n";
					$out .= $com.' Creating relacionships for '.$row[0]."\n";
					$out .= $com."\n";
					$out .= 'ALTER TABLE ONLY '.$row[0].' ADD CONSTRAINT '.$row[1].' '.$row[2].";\n"; 
				}
				$out .= "\n";
			}
		}

		return $out;
	}

}

?>